﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Weiz.TaskManager.Common;
using Weiz.TaskManager.DBUtility;
using Weiz.TaskManager.Models;

namespace Weiz.TaskManager.DAL
{
    public  class SpecificationsDAL
    {

        public bool Add(SpecificationsModel model)
        {
            var sql = @"INSERT INTO [Specifications]
           ([ID]
           ,[Name]
           ,[Remark]
           ,[IsDelete]
           ,[CreateDate])
     VALUES
           (@ID
           ,@Name
           ,@Remark
           ,@IsDelete
           ,getdate())";

            object param = new
            {
                ID = model.ID,
                Name = model.Name,
                Remark = model.Remark,
                IsDelete = model.IsDelete
            };
            return SQLHelper.ExecuteNonQuery(sql, param) > 0;
        }



        public PageOf<SpecificationsModel> GetSpecificationsList(int pageNo, int pageSize)
        {

            var QUERY_SQL = @"
(SELECT [ID]
      ,[Name]
      ,[Remark]
      ,[IsDelete]
      ,[CreateDate]
  FROM [Specifications])pp";
            string SQL = string.Format(@" select * from (select ROW_NUMBER() OVER(order by pp.CreateDate desc) AS RowNum,* from {0}
										) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;",
                                  QUERY_SQL);

            SQL += string.Format(@" SELECT COUNT(1) FROM {0};", QUERY_SQL);

            object param = new { pageIndex = pageNo, pageSize = pageSize };
            DataSet ds = SQLHelper.FillDataSet(SQL, param);
            return new PageOf<SpecificationsModel>()
            {
                PageIndex = pageNo,
                PageSize = pageSize,
                Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
                Items = DataMapHelper.DataSetToList<SpecificationsModel>(ds)
            };
         
        }



    }
}
